import pandas as pd
import mysql_util as sql_util
import mysql_util_starrocks as starrocks_util
import json
import math

"""查询用户是否分配配权限"""


def test(uid):
    sql = "select count(1) total from master_data.cp_utm_event_log a where 1= 1 and a.create_time > '2024-09-16' and a.appid = '401' and a.uid = '{}' \
        and a.utm_souce in ('Broilerchicken_Numberofuserentries')".format(uid)
    rs = starrocks_util.select_by_sql(sql)
    rs = json.loads(rs)
    # print(rs)
    return rs[0]['total']


def test2(uid):
    sql = "select count(1) total from master_data.cp_utm_event_log a where 1= 1 and a.create_time > '2024-09-16' and a.appid = '401' and a.uid = '{}'".format(
        uid)
    rs = starrocks_util.select_by_sql(sql)
    rs = json.loads(rs)
    # print(rs)
    return rs[0]['total']


def test3(uid):
    sql = "select count(1) total from master_data.cp_utm_event_log a where 1= 1 and a.create_time > '2024-09-16' and a.uid = '{}' \
        and a.utm_souce in ('ZF_Board_TaskList_Click','ZF_Board_CatDes_Click','ZF_DetailPage_SubMit_Commit_Click')".format(
        uid)
    rs = starrocks_util.select_by_sql(sql)
    rs = json.loads(rs)
    # print(rs)
    return rs[0]['total']


def test4(uid):
    sql = "select DATE_FORMAT(max(create_time),'%Y-%m-%d %H:%i:%s') total from master_data.cp_utm_event_log a where 1= 1 and a.create_time > '2024-01-01' and a.appid = '401' and a.uid = '{}'".format(
        uid)
    rs = starrocks_util.select_by_sql(sql)
    rs = json.loads(rs)
    # print(rs)
    d = rs[0]['total']
    if rs[0]['total'] is None:
        d = ''
    return d


if __name__ == '__main__':
    file_path = 'C:/Users/yangjianzhang/Desktop/肉鸡环控派单人员清单.xls'
    # 使用read_excel函数读取Excel文件并将其存储为DataFrame对象
    df = pd.read_excel(file_path)

    # 打印读取到的数据
    # print(df)
    print(type(df))
    mobiles = []
    flags = []
    data_1s = []
    data_2s = []
    data_3s = []
    data_4s = []
    uids = []

    for index, row in df.iterrows():
        mobile = row['手机号ZTEL']
        mobiles.append(mobile)
        flag = '否'
        data_1 = ''
        data_2 = ''
        data_3 = ''
        data_4 = ''
        uid = ''
        if math.isnan(mobile):
            # mobile = 0
            print('行号%d手机号有问题', (index))
        try:
            mobile = str(int(mobile))
            # print(type(mobile))
            print(mobile)
            # 查询用户是否绑定了权限
            sql = "	select external_id from pc_user where mobile = '{}' order by id limit 1".format(mobile)
            rs = sql_util.select_by_sql(sql)
            rs = json.loads(rs)
            print(rs)
            if len(rs) > 0:
                uid = int(rs[0]['external_id'])
                flag = '是'
                # 测试埋点
                data_1 = test(uid)
                data_2 = test2(uid)
                data_3 = test3(uid)
                data_4 = test4(uid)

        except Exception as e:
            print(e)
            print(index)

        flags.append(flag)
        data_1s.append(data_1)
        data_2s.append(data_2)
        data_3s.append(data_3)
        data_4s.append(data_4)
        uids.append(uid)

    excel_data = {}
    excel_data['手机号'] = mobiles
    excel_data['uid'] = uids
    excel_data['是否上养殖系统'] = flags
    excel_data['近一月登录次数'] = data_1s
    excel_data['近一月使用次数(禽蛋博士)'] = data_2s
    excel_data['近一月使用次数(战房派单)'] = data_3s
    excel_data['最近一次使用日期'] = data_4s

    df = pd.DataFrame(excel_data)
    path = "C:/Users/yangjianzhang/Desktop/"
    df.to_excel(path + 'test_rs.xlsx', index=False)
